class NoteSqlMap(object):
    FIND_ALL_CLASSIFY = "SELECT catalog_id,catalog_name FROM aoa_catalog"
    note_add = "insert into aoa_note_list(attach_id,content,create_time,title,catalog_id,createman_id,receiver) values({attach_id},'{content}',now(),'{title}',{catalog_id},{createman_id},'{receiver}')"
    note_add_no = "insert into aoa_note_list(attach_id,content,create_time,title,catalog_id,createman_id,receiver) values({attach_id},'{content}',now(),'{title}',{catalog_id},{createman_id},{receiver})"
    check_name = "select real_name,user_name from aoa_user where user_id={user_id}"
    
    my_note_list = "select t1.createman_id,t4.real_name,t3.catalog_name,t1.note_id,t1.title,t1.content,t1.create_time from aoa_note_list as t1 inner join aoa_catalog as t3 on t1.catalog_id=t3.catalog_id inner join aoa_user as t4 on t1.createman_id=t4.user_id where t1.createman_id={user_id}"
    
    note_read_name = "select t2.real_name from aoa_user as t2 where t2.user_id=any(select distinct t3.user_id from aoa_note_read as t3 where t3.note_id={note_id} and t3.is_read=1)"
    note_read_count = "select count(distinct t1.user_id) as read_times from aoa_note_read as t1 where t1.note_id={note_id} and t1.is_read=1"
    
    subordinates_id_list_all = "SELECT u.user_id from aoa_user u where u.user_id<>{user_id}"
    
    FIND_ROLE_BY_ID = "SELECT aoa_role_.role_id,aoa_role_.role_value from aoa_user inner join aoa_role_ on aoa_user.role_id = aoa_role_.role_id where user_id={user_id}"
    
    subordinates_id_list = "select t1.user_id from aoa_user as t1 inner join aoa_dept as t3 on t3.dept_id=t1.dept_id inner join aoa_role_ as t4 on t4.role_id=t1.role_id where t4.role_value>(select role_value from aoa_user inner join aoa_role_ on aoa_user.role_id=aoa_role_.role_id where user_id={user_id}) and t1.dept_id=(select dept_id from aoa_user where user_id={user_id})"
    
    my_note_filter = "select t1.createman_id,t4.real_name,t3.catalog_name,t1.note_id,t1.title,t1.content,t1.create_time from aoa_note_list as t1 inner join aoa_catalog as t3 on t1.catalog_id=t3.catalog_id inner join aoa_user as t4 on t1.createman_id=t4.user_id"
    
    subordinates_note_filter = "select t1.createman_id,t4.real_name,t3.catalog_name,t1.note_id,t1.title,t1.content,t1.create_time from aoa_note_list as t1 inner join aoa_catalog as t3 on t1.catalog_id=t3.catalog_id inner join aoa_user as t4 on t1.createman_id=t4.user_id where t1.createman_id={subordinates_id}"
    # my_read = "select note_id from aoa_note_read where user_id={user_id} and is_read="

    check_note = "select t1.createman_id,t4.real_name,t3.catalog_name,t1.note_id,t1.title,t1.content,t1.create_time,t5.attachment_name,t5.attachment_id as attach_id from aoa_note_list as t1 inner join aoa_catalog as t3 on t1.catalog_id=t3.catalog_id inner join aoa_user as t4 on t1.createman_id=t4.user_id left join aoa_attachment_list as t5 on t1.attach_id=t5.attachment_id where t1.note_id={note_id}"
    
    CHECK_ATTACH = "select * from aoa_attachment_list where attachment_id={attach_id}"
    GET_ID = "select @@IDENTITY as id"
    INSERT_READ = "insert into aoa_note_read(is_read, user_id, note_id) values(1,{user_id},{note_id})"

    CHECK_READ = "SELECT id FROM aoa_note_read WHERE user_id={user_id} AND note_id={note_id}"

    FILTER_PARAMS = {
        "query_str": "t1.title like '%{query_str}%'",
        "catalog_id": "t1.catalog_id={catalog_id}",
        # "isread": "t1.note_id in (select note_id from aoa_note_read where user_id={reader_id})",
        "receiver_to_me": "t1.receiver like '%{user_name}%'"
    }
